package tablization;

import java.util.Hashtable;
import java.util.Vector;

import java.sql.ResultSet;

import dbmanager.DBManager;
import utility.Input;

/**
 *  @author Shweta
 **/
public class TableDeletion extends SubTable
{

	public Vector<Object> deleteVector = new Vector<Object>() ;
	
	public TableDeletion(DBManager sqlDB)
	{
		super.setDBManager(sqlDB);
	}
	
	
	/**
	 * this function checks for exisence of any row in case of unique-valued table
	 * @param id - is string value whose row existence to be checked
	 * @param tablename - is the name of table in which row's existence to be checked
	 */
	private void checkRowExistence(String id,String tablename)
	{
		
		int counter = 0;
		StringBuffer delQuery = new StringBuffer();
		ResultSet rs;
		String query = "select * from "+ tablename + " where id = "+ id ;
		rs = sqlDB.getSelect(query);
		
		//retrieve columns of given table
		Hashtable<Object,Object> hashtable = new Hashtable<Object, Object>();				
		
		hashtable.put(PID_NAME, "column");
		hashtable.put(keyName, "mid");
		hashtable.put(valueName, gin.getId(tablename));
		
		String [] columnArray= getArray(hashtable);
		
		try
		{
			while(rs.next())
			{
				for(int i=0; i<columnArray.length; i++)
				{
					String column = gin.getItem(columnArray[i].split(Input.SEPARATOR)[2]);
					String value = rs.getString(column);
					
						if(value.equals(""))
						{
							delQuery.append(" AND ");
							delQuery.append(column);
							delQuery.append(" = '");
							delQuery.append(value);
							delQuery.append("'");
							
							counter++;
						}
				}
				if(counter == (columnArray.length-1))
				{
					String deleteQuery = "DELETE FROM "+tablename+" WHERE id = "+ id + delQuery.toString();
					sqlDB.getDelete(deleteQuery);
					counter = 0;
				}
			}
		}
		catch (Exception e) {
			// TODO: handle exception
		}		
		
		
		 System.out.println("Table row deleted successfully");
	}
	
	
	/**
	 * this function deletes values from columns if requested
	 * @param vector containing properties to be deleted
	 */
	@SuppressWarnings("unchecked")
	public boolean getDelete(Vector<Object> vector)
	{
		
		int x =0;
		String oldvalue ="";
		boolean flag = false;
		Vector<Object> oldVector = vector ;
		
		
		Hashtable<Object,Object> hasht = (Hashtable<Object, Object>)oldVector.get(0);
		
		String midName = hasht.get("mid").toString();
		
		//get array of tables for given mid name
		Hashtable<Object,Object> arrayHash = new Hashtable<Object, Object>();				
		
		arrayHash.put(PID_NAME, "tablization");
		arrayHash.put(keyName, "mid");
		arrayHash.put(valueName, midName);
		
		String [] tableArray= getArray(arrayHash);	
		
		if(tableArray.length != 0)
		{
			for(int i=0;i<oldVector.size();i++)
			{				
				Hashtable<Object,Object> tempOldHash = (Hashtable<Object, Object>)oldVector.get(i);
				String column = tempOldHash.get("pid").toString();
				
				//get array of tables having given column
				arrayHash = new Hashtable<Object, Object>();				
				
				arrayHash.put(PID_NAME, "column");
				arrayHash.put(keyName, "pv");
				arrayHash.put(valueName, column);
				
				String [] columnTableArray= getArray(arrayHash);
				
				//get tablename to update values of given column
				String tablename = getTableName(tableArray,columnTableArray);
				
				if(!tablename.equals("") )
				{	
					
					column = gin.getItem(column);
					
					//get value of the given column
					oldvalue = getColumnValue(tempOldHash);
					
					//checks whether table is unique/multiple valued
					if(checkTableCondition(tablename) == false)
					{
						//set value of given table to null						
						Hashtable<Object, Object> oldHash = new Hashtable<Object, Object>();
						oldHash.put("id",midName);
						oldHash.put(tablename +"."+column , oldvalue);
					
						Hashtable<Object, Object> newHash = new Hashtable<Object, Object>();
						newHash.put("id",midName);
						String newvalue = "";
						newHash.put(tablename +"."+column , newvalue);
						
						x = sqlDB.getUpdate(tablename, newHash, oldHash);
						checkRowExistence(midName, tablename);						
					}
					else
					{
						String deleteQuery = "DELETE FROM "+tablename+" WHERE id = '"+midName+ "' AND "+column+ " = '" + oldvalue+"'";
						x = sqlDB.getDelete(deleteQuery);
					}
					
					if(x>0)
					{
						oldVector.remove(i);						
						try	{
							i--;
						} catch (Exception e) {
							i=0;
						}						
					}
				}				
			}
			if(oldVector.size() != 0)
				deleteVector = oldVector;
			
			flag = true;
		}
				
		
		 return flag;
	}
	
	
	public static void main(String[] args) 
	{

		DBManager sqldb = new DBManager();
		sqldb.getConnect();
		
		TableDeletion td = new TableDeletion(sqldb);
		Vector<Object> vector = new Vector<Object>() ;
		Hashtable<Object,Object> hasht = new Hashtable<Object, Object>();
		
		hasht.put("mid","Shweta");
		hasht.put("pid","subject");
		hasht.put("pv","Maths");
		
		vector.add(td.convertInMasterId(hasht));
	
		String type = "human2";
		boolean flag = false;
		
		if(td.checkTableExistence(type) == true)
		{
			flag = td.getDelete(vector);
		}
		
		if(flag == true)
		{
			System.out.println("Table row deleted successfully");
			if(!td.deleteVector.isEmpty())
				System.out.println("delete from pl");
				//send to pl to delete
		}
		else
		{
			System.out.println("Row not deleted");
		    //send to pl to delete
		}
	}
}
